package sample;

import javafx.collections.ObservableList;
import javafx.fxml.FXML;
import javafx.scene.control.ListView;

import java.nio.charset.StandardCharsets;
import java.sql.*;

public class QueryBilibili {
    public static boolean value;//用来判断是否在数据库中

    static {
        value = false;
    }

    public static String Tostring(String inHex) {//16进制字符转回字符串
        String array[]=inHex.split("");
        String s="";
        for(int i=0;i<array.length;i++){
            if(i%2!=0){
                s=s+array[i]+" ";
            }else{
                s+=array[i];
            }
        }
        String str="";
        String[] hex = s.split(" ");//将接收的字符串按空格分割成数组byte[ ] byteArray=new byte[ hex.length];
        if(hex.length!=0){
            byte[] byteArray = new byte[hex.length];
            for (int i = 0; i < hex.length; i++) {
                //parseInt()方法用于将字符串参数作为有符号的n进制整数进行解析
                byteArray[i] = (byte) Integer.parseInt(hex[i], 16);
            }
            str=new String(byteArray, StandardCharsets.UTF_8);
        }
        return str;
    }


    public static String Tohex(String s){//字符串转16进制
        byte[] b=s.getBytes();
        String hs = "";
        String stmp = "";
        for (int i = 0; i < b.length; i++) {
            stmp = (Integer.toHexString(b[i] & 0XFF));
            if (stmp.length() == 1) {
                hs = hs + "0" + stmp ;
            } else {
                hs = hs + stmp ;
            }
        }
        return hs.toUpperCase();
    }
    public static String ITohex(int a){//整数转16进制
        String s=String.valueOf(a);
        byte[] b=s.getBytes();
        String hs = "";
        String stmp = "";
        for (int i = 0; i < b.length; i++) {
            stmp = (Integer.toHexString(b[i] & 0XFF));
            if (stmp.length() == 1) {
                hs = hs + "0" + stmp ;
            } else {
                hs = hs + stmp;
            }
        }
        return hs.toUpperCase();
    }
    @FXML//从数据库获取数据并添加到ListView
    public static void dataPosiionDatabase(ListView myList,ObservableList myListData,ResultSet rs) throws SQLException {
            myListData.add("从数据库中提取:");
            myListData.add(Tostring(rs.getString("bvid")));
            myListData.add(Tostring(rs.getString("title")));
            myListData.add(Tostring(rs.getString("pic")));
            myListData.add(Tostring(rs.getString("desc")));
            myListData.add(Tostring(rs.getString("ownername")));
            myListData.add(Tostring(rs.getString("ownerface")));
            myListData.add(Tostring(rs.getString("view")));
            myListData.add(Tostring(rs.getString("danmaku")));
            myListData.add(Tostring(rs.getString("reply")));
            myListData.add(Tostring(rs.getString("favorite")));
            myListData.add(Tostring(rs.getString("coin")));
            myListData.add(Tostring(rs.getString("share")));
            myListData.add(Tostring(rs.getString("like")));
            myListData.add(Tostring(rs.getString("date")));
            myListData.add(Tostring(rs.getString("videourl")));
            myList.setItems(myListData);
    }
    @FXML//从借口获取数据并添加到ListView
    public static void dataPosiionAPI(ListView myList,ObservableList myListData,DataBilibili data) throws SQLException {
        myListData.add("从接口提取:");
        myListData.add(data.getData().getBvid());
        myListData.add(data.getData().getTitle());
        myListData.add(data.getData().getPic());
        myListData.add(data.getData().getDesc());
        myListData.add(data.getData().getOwnername());
        myListData.add(data.getData().getOwnerface());
        myListData.add(data.getData().getView());
        myListData.add(data.getData().getDanmaku());
        myListData.add(data.getData().getReply());
        myListData.add(data.getData().getFavorite());
        myListData.add(data.getData().getCoin());
        myListData.add(data.getData().getShare());
        myListData.add(data.getData().getLike());
        myListData.add(data.getData().getDate());
        myListData.add(data.getData().getVideourl());
        myList.setItems(myListData);
    }
    @FXML
    public void querybilibili(String text,DataBilibili data,ListView myList,ObservableList myListData) {//对数据的查询，写入等数据库操作
        Controller bilibiliController = new Controller();
        String mysqlDriver = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/yiqing?useUnicode=utf-8&useSSL=true";
        String username = "root";
        String password = "7878478";
        Connection con = null;
        Statement sql;
        ResultSet rs;
        int n = 5;

        try {
            Class.forName(mysqlDriver);
            con = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            sql = con.createStatement();
            String sqlQuery = "select * from bili";
            rs = sql.executeQuery(sqlQuery);

//            int num=0;
            while (rs.next()) {
                String s = rs.getString("bvid");
                if ((Tostring(s)).equals(text)) {
                    value = true;
                    System.out.println("数据存在该数据,从数据库中搜索数据");
                    dataPosiionDatabase(myList, myListData, rs);
                }
            }
            if (value == false) {
                System.out.println("数据库中不存在改数据,从接口获取数据,并写入数据库中");
                dataPosiionAPI(myList, myListData, data);
                String sqlInsert = "Insert into bili values" + "('" + Tohex(data.getData().getBvid()) + "','" + Tohex(data.getData().getTitle()) + "','"
                        + Tohex(data.getData().getPic()) + "','" + Tohex(data.getData().getDesc()) + "','" + Tohex(data.getData().getOwnername()) + "','"
                        + Tohex(data.getData().getOwnerface()) + "','" + ITohex(data.getData().getView()) + "','" + ITohex(data.getData().getDanmaku()) + "','"
                        + ITohex(data.getData().getReply()) + "','" + ITohex(data.getData().getFavorite()) + "','" + ITohex(data.getData().getCoin()) + "','"
                        + ITohex(data.getData().getShare()) + "','" + ITohex(data.getData().getLike()) + "','" + Tohex(data.getData().getDate()) + "','"
                        + Tohex(data.getData().getVideourl()) + "')";
                sql.executeUpdate(sqlInsert);
                System.out.println("插入成功");
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}